﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Members
{
    internal static class MemberDAL
    {
        internal static MemberEntity GetMemberByReader(IDataReader dr)
        {
            MemberEntity member = new MemberEntity();

            member.MemberID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            member.Email = dr.IsDBNull(1) ? null : dr.GetString(1);
            member.Password = dr.IsDBNull(2) ? null : dr.GetString(2);
            member.ImgUrl = dr.IsDBNull(3) ? "Null.jpg" : dr.GetString(3);
            member.Nickname = dr.IsDBNull(4) ? null : dr.GetString(4);
            member.Name = dr.IsDBNull(5) ? null : dr.GetString(5);
            member.Sex = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            member.Birthday = Util.ConverDateTime(dr.GetValue(7));
            member.QQ = dr.IsDBNull(8) ? null : dr.GetString(8);
            member.Phone = dr.IsDBNull(9) ? null : dr.GetString(9);
            member.ProvinceID = dr.IsDBNull(10) ? 0 : dr.GetInt32(10);
            member.CityID = dr.IsDBNull(11) ? 0 : dr.GetInt32(11);
            member.AreaID = dr.IsDBNull(12) ? 0 : dr.GetInt32(12);
            member.Address = dr.IsDBNull(13) ? null : dr.GetString(13);
            member.Profession = dr.IsDBNull(14) ? 0 : dr.GetInt32(14);
            member.Education = dr.IsDBNull(15) ? 0 : dr.GetInt32(15);
            member.Income = dr.IsDBNull(16) ? 0 : dr.GetInt32(16);
            member.Marriage = dr.IsDBNull(17) ? 0 : dr.GetInt32(17);
            member.LandingNumber = dr.IsDBNull(18) ? 0 : dr.GetInt32(18);
            member.LandDateTime = Util.ConverDateTime(dr.GetValue(19));
            member.Created = Util.ConverDateTime(dr.GetValue(20));
            member.State = dr.IsDBNull(21) ? 0 : dr.GetInt32(21);
            member.ProvinceName = dr.IsDBNull(22) ? null : dr.GetString(22);
            member.CityName = dr.IsDBNull(23) ? null : dr.GetString(23);
            member.AreaName = dr.IsDBNull(24) ? null : dr.GetString(24);

            return member;
        }

        internal static int GetMemberCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectMember = @"SELECT COUNT(Member.[MemberID]) 
                    FROM [Member] LEFT OUTER JOIN
                      Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                      Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                      Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectMember, ref parameterlst);
            DbCommand SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
            DbHelper.PackDbCommandByDbParameters(SelectMemberCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectMemberCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<MemberEntity> GetMemberList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<MemberEntity> memberlst = new List<MemberEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectMember = @"
                    SELECT [MemberID],[Email],[Password],[ImgUrl],[Nickname],[Name],[Sex],[Birthday],[QQ],[Phone],
                        [ProvinceID],[CityID],[AreaID],[Address],[Profession],[Education],[Income],[Marriage],[LandingNumber],
                        [LandDateTime],[Created],[State],ProvinceName,CityName,AreaName
                    FROM (
                        SELECT [Member].[MemberID],[Member].[Email],[Member].[Password],[Member].[ImgUrl],[Member].[Nickname],
                            [Member].[Name],[Member].[Sex],[Member].[Birthday],[Member].[QQ],[Member].[Phone],[Member].[ProvinceID],
                            [Member].[CityID],[Member].[AreaID],[Member].[Address],[Member].[Profession],[Member].[Education],
                            [Member].[Income],[Member].[Marriage],[Member].[LandingNumber],[Member].[LandDateTime],
                            [Member].[Created],[Member].[State],ProvinceArea.Name AS ProvinceName, 
                            CityArea.Name AS CityName, AreaArea.Name AS AreaName, ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectMember += " " + sortField + " " + sortMode + ",";
            SelectMember += @" [Member].[MemberID] Desc
                    ) AS rowNum 
                        FROM [Member] LEFT OUTER JOIN
                          Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                          Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                          Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectMember, ref parameterlst);
            SelectMember += @"
                    ) AS [Member] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectMemberCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
            {
                while (dr.Read())
                {
                    memberlst.Add(GetMemberByReader(dr));
                }
            }

            return memberlst;
        }

        internal static List<MemberEntity> GetMemberList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<MemberEntity> memberlst = new List<MemberEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectMember = @"
                    SELECT [Member].[MemberID],[Member].[Email],[Member].[Password],[Member].[ImgUrl],[Member].[Nickname],
                        [Member].[Name],[Member].[Sex],[Member].[Birthday],[Member].[QQ],[Member].[Phone],[Member].[ProvinceID],
                        [Member].[CityID],[Member].[AreaID],[Member].[Address],[Member].[Profession],[Member].[Education],
                        [Member].[Income],[Member].[Marriage],[Member].[LandingNumber],[Member].[LandDateTime],
                        [Member].[Created],[Member].[State],ProvinceArea.Name AS ProvinceName, 
                        CityArea.Name AS CityName, AreaArea.Name AS AreaName
                    FROM [Member] LEFT OUTER JOIN
                      Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                      Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                      Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectMember, ref parameterlst);
            SelectMember += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectMember += " " + sortField + " " + sortMode + ",";
            SelectMember += " [Member].[MemberID] Desc ";

            DbCommand SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
            DbHelper.PackDbCommandByDbParameters(SelectMemberCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
            {
                while (dr.Read())
                {
                    memberlst.Add(GetMemberByReader(dr));
                }
            }

            return memberlst;
        }

        internal static MemberEntity GetMember(int memberID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            MemberEntity member = null;

            string SelectMember = @"
                    SELECT [Member].[MemberID],[Member].[Email],[Member].[Password],[Member].[ImgUrl],[Member].[Nickname],
                        [Member].[Name],[Member].[Sex],[Member].[Birthday],[Member].[QQ],[Member].[Phone],[Member].[ProvinceID],
                        [Member].[CityID],[Member].[AreaID],[Member].[Address],[Member].[Profession],[Member].[Education],
                        [Member].[Income],[Member].[Marriage],[Member].[LandingNumber],[Member].[LandDateTime],
                        [Member].[Created],[Member].[State],ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName, 
                        AreaArea.Name AS AreaName,[Member].[ValidateCode],[Member].[ValidateDateTime]
                    FROM [Member] LEFT OUTER JOIN
                      Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                      Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                      Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID
                    WHERE [Member].[MemberID]=@MemberID
                ";

            DbCommand SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
            db.AddInParameter(SelectMemberCommand, "@MemberID", DbType.Int32, memberID);

            using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
            {
                if (dr.Read())
                {
                    member = GetMemberByReader(dr);
                    member.ValidateCode = dr["ValidateCode"] == DBNull.Value ? null : dr["ValidateCode"].ToString();
                    member.ValidateDateTime = Util.ConverDateTime(dr["ValidateDateTime"]);
                }
            }

            return member;
        }

        internal static int InsertMember(MemberEntity member)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertMember = @"INSERT INTO [Member] 
				([Email],[Password],[ImgUrl],[Nickname],[Name],[Sex],[Birthday],[QQ],[Phone],[ProvinceID],[CityID],
                [AreaID],[Address],[Profession],[Education],[Income],[Marriage],[LandingNumber],[LandDateTime],[Created],
                [State],[ValidateCode])
				 VALUES (@Email,@Password,@ImgUrl,@Nickname,@Name,@Sex,@Birthday,@QQ,@Phone,@ProvinceID,@CityID,@AreaID,
                @Address,@Profession,@Education,@Income,@Marriage,@LandingNumber,@LandDateTime,@Created,@State,@ValidateCode)";

            DbCommand InsertMemberCommand = db.GetSqlStringCommand(InsertMember);
            db.AddInParameter(InsertMemberCommand, "@Email", DbType.String, member.Email);
            db.AddInParameter(InsertMemberCommand, "@Password", DbType.String, member.Password);
            db.AddInParameter(InsertMemberCommand, "@ImgUrl", DbType.String, member.ImgUrl);
            db.AddInParameter(InsertMemberCommand, "@Nickname", DbType.String, member.Nickname);
            db.AddInParameter(InsertMemberCommand, "@Name", DbType.String, member.Name);
            db.AddInParameter(InsertMemberCommand, "@Sex", DbType.Int32, member.Sex);
            db.AddInParameter(InsertMemberCommand, "@Birthday", DbType.DateTime, member.Birthday);
            db.AddInParameter(InsertMemberCommand, "@QQ", DbType.String, member.QQ);
            db.AddInParameter(InsertMemberCommand, "@Phone", DbType.String, member.Phone);
            db.AddInParameter(InsertMemberCommand, "@ProvinceID", DbType.Int32, member.ProvinceID);
            db.AddInParameter(InsertMemberCommand, "@CityID", DbType.Int32, member.CityID);
            db.AddInParameter(InsertMemberCommand, "@AreaID", DbType.Int32, member.AreaID);
            db.AddInParameter(InsertMemberCommand, "@Address", DbType.String, member.Address);
            db.AddInParameter(InsertMemberCommand, "@Profession", DbType.Int32, member.Profession);
            db.AddInParameter(InsertMemberCommand, "@Education", DbType.Int32, member.Education);
            db.AddInParameter(InsertMemberCommand, "@Income", DbType.Int32, member.Income);
            db.AddInParameter(InsertMemberCommand, "@Marriage", DbType.Int32, member.Marriage);
            db.AddInParameter(InsertMemberCommand, "@LandingNumber", DbType.Int32, member.LandingNumber);
            db.AddInParameter(InsertMemberCommand, "@LandDateTime", DbType.DateTime, member.LandDateTime);
            db.AddInParameter(InsertMemberCommand, "@Created", DbType.DateTime, member.Created);
            db.AddInParameter(InsertMemberCommand, "@State", DbType.Int32, member.State);
            db.AddInParameter(InsertMemberCommand, "@ValidateCode", DbType.String, member.ValidateCode);

            result = db.ExecuteNonQuery(InsertMemberCommand);

            return result;
        }

        internal static int UpdateMember(MemberEntity member)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateMember = @"UPDATE [Member] 
				 SET [Email]=@Email,[Password]=@Password,[ImgUrl]=@ImgUrl,[Nickname]=@Nickname,[Name]=@Name,[Sex]=@Sex,
                    [Birthday]=@Birthday,[QQ]=@QQ,[Phone]=@Phone,[ProvinceID]=@ProvinceID,[CityID]=@CityID,[AreaID]=@AreaID,
                    [Address]=@Address,[Profession]=@Profession,[Education]=@Education,[Income]=@Income,[Marriage]=@Marriage,
                    [Created]=@Created,[State]=@State 
				 WHERE [MemberID] = @MemberID";

            DbCommand UpdateMemberCommand = db.GetSqlStringCommand(UpdateMember);
            db.AddInParameter(UpdateMemberCommand, "@MemberID", DbType.Int32, member.MemberID);
            db.AddInParameter(UpdateMemberCommand, "@Email", DbType.String, member.Email);
            db.AddInParameter(UpdateMemberCommand, "@Password", DbType.String, member.Password);
            db.AddInParameter(UpdateMemberCommand, "@ImgUrl", DbType.String, member.ImgUrl);
            db.AddInParameter(UpdateMemberCommand, "@Nickname", DbType.String, member.Nickname);
            db.AddInParameter(UpdateMemberCommand, "@Name", DbType.String, member.Name);
            db.AddInParameter(UpdateMemberCommand, "@Sex", DbType.Int32, member.Sex);
            db.AddInParameter(UpdateMemberCommand, "@Birthday", DbType.DateTime, member.Birthday);
            db.AddInParameter(UpdateMemberCommand, "@QQ", DbType.String, member.QQ);
            db.AddInParameter(UpdateMemberCommand, "@Phone", DbType.String, member.Phone);
            db.AddInParameter(UpdateMemberCommand, "@ProvinceID", DbType.Int32, member.ProvinceID);
            db.AddInParameter(UpdateMemberCommand, "@CityID", DbType.Int32, member.CityID);
            db.AddInParameter(UpdateMemberCommand, "@AreaID", DbType.Int32, member.AreaID);
            db.AddInParameter(UpdateMemberCommand, "@Address", DbType.String, member.Address);
            db.AddInParameter(UpdateMemberCommand, "@Profession", DbType.Int32, member.Profession);
            db.AddInParameter(UpdateMemberCommand, "@Education", DbType.Int32, member.Education);
            db.AddInParameter(UpdateMemberCommand, "@Income", DbType.Int32, member.Income);
            db.AddInParameter(UpdateMemberCommand, "@Marriage", DbType.Int32, member.Marriage);
            db.AddInParameter(UpdateMemberCommand, "@Created", DbType.DateTime, member.Created);
            db.AddInParameter(UpdateMemberCommand, "@State", DbType.Int32, member.State);

            result = db.ExecuteNonQuery(UpdateMemberCommand);

            return result;
        }

        internal static int DeleteMember(int memberID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteMember = @"
                DELETE FROM [Member] WHERE MemberID=@MemberID";

            DbCommand DeleteMemberCommand = db.GetSqlStringCommand(DeleteMember);
            db.AddInParameter(DeleteMemberCommand, "@MemberID", DbType.Int32, memberID);
            result = db.ExecuteNonQuery(DeleteMemberCommand);

            return result;
        }

        internal static int LandingNumber(int memberID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetMember = @"UPDATE [Member]
                    SET [LandingNumber] = [LandingNumber]+1
                    WHERE [MemberID] = @MemberID";

            DbCommand SetMemberCommand = db.GetSqlStringCommand(SetMember);
            db.AddInParameter(SetMemberCommand, "@MemberID", DbType.Int32, memberID);
            result = db.ExecuteNonQuery(SetMemberCommand);

            return result;
        }

        internal static MemberEntity GetMemberByEmail(string email)
        {
            Database db = DatabaseFactory.CreateDatabase();
            MemberEntity member = null;

            string SelectMember = @"
                    SELECT [Member].[MemberID],[Member].[Email],[Member].[Password],[Member].[ImgUrl],[Member].[Nickname],
                        [Member].[Name],[Member].[Sex],[Member].[Birthday],[Member].[QQ],[Member].[Phone],[Member].[ProvinceID],
                        [Member].[CityID],[Member].[AreaID],[Member].[Address],[Member].[Profession],[Member].[Education],
                        [Member].[Income],[Member].[Marriage],[Member].[LandingNumber],[Member].[LandDateTime],
                        [Member].[Created],[Member].[State],ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName, 
                        AreaArea.Name AS AreaName,[Member].[ValidateCode],[Member].[ValidateDateTime]
                    FROM [Member] LEFT OUTER JOIN
                      Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                      Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                      Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID
                    WHERE [Member].Email=@Email OR [Member].[Nickname]=@Email";

            DbCommand SelectAdminCommand = db.GetSqlStringCommand(SelectMember);
            db.AddInParameter(SelectAdminCommand, "@Email", DbType.String, email);

            using (IDataReader dr = db.ExecuteReader(SelectAdminCommand))
            {
                if (dr.Read())
                {
                    member = GetMemberByReader(dr);
                    member.ValidateCode = dr["ValidateCode"] == DBNull.Value ? null : dr["ValidateCode"].ToString();
                    member.ValidateDateTime = Util.ConverDateTime(dr["ValidateDateTime"]);
                }
            }

            return member;
        }

        internal static int Enter(string email, string password)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SelectEnter = @"
                /*   1：成功  2：会员不存在  3：密码错误  */
	            DECLARE @result AS INT
	            SET @result=1
	
	            IF EXISTS(SELECT [MemberID] FROM [Member] WHERE [Email]=@Email OR [Nickname]=@Email)
	            BEGIN
			        IF EXISTS(SELECT [MemberID] FROM [Member] WHERE ([Email]=@Email OR [Nickname]=@Email) AND [Password]=@Password)
			        BEGIN
				        SET @result=1
			        END
			        ELSE
			        BEGIN
				        SET @result=3
			        END
	            END
	            ELSE
	            BEGIN
		            SET @result=2
	            END
	            SELECT @result";

            DbCommand SetMemberPasswordCommand = db.GetSqlStringCommand(SelectEnter);
            db.AddInParameter(SetMemberPasswordCommand, "@Password", DbType.String, password);
            db.AddInParameter(SetMemberPasswordCommand, "@Email", DbType.String, email);
            object obj = db.ExecuteScalar(SetMemberPasswordCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());
            return result;
        }

        internal static int SetMemberPassword(int memberID, string oldPassword, string newPassword)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetMemberPassword = @"
            /*   1：成功  2：会员不存在  3：原密码错误   */
	        DECLARE @result AS INT
	        SET @result=0
        	
	        IF EXISTS(SELECT [MemberID] FROM [Member] WHERE MemberID=@MemberID)
	        BEGIN
		        IF EXISTS(SELECT [MemberID] FROM [Member] WHERE MemberID=@MemberID AND [Password]=@OldPassword)
		        BEGIN
			        UPDATE [Member] SET [Password] = @NewPassword WHERE [MemberID] = @MemberID
			        SET @result=1
		        END
		        ELSE
		        BEGIN
			        SET @result=3
		        END
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
	        SELECT @result";

            DbCommand SetMemberPasswordCommand = db.GetSqlStringCommand(SetMemberPassword);
            db.AddInParameter(SetMemberPasswordCommand, "@OldPassword", DbType.String, oldPassword);
            db.AddInParameter(SetMemberPasswordCommand, "@NewPassword", DbType.String, newPassword);
            db.AddInParameter(SetMemberPasswordCommand, "@MemberID", DbType.Int32, memberID);
            object obj = db.ExecuteScalar(SetMemberPasswordCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());
            return result;
        }

        internal static int ResetPass(int memberID, string newpass)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string ResetPass = @"
            /* 1：成功 2：没有此会员 */
	        DECLARE @result AS INT
	        SET @result=1
        	
	        IF EXISTS(SELECT [MemberID] FROM [Member] WHERE [MemberID]=@MemberID)
	        BEGIN
		        UPDATE [Member] SET [Password]=@NewPass WHERE [MemberID]=@MemberID
		        SET @result=1
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
        	
	        SELECT @result";

            DbCommand ResetPassCommand = db.GetSqlStringCommand(ResetPass);
            db.AddInParameter(ResetPassCommand, "@MemberID", DbType.Int32, memberID);
            db.AddInParameter(ResetPassCommand, "@NewPass", DbType.String, newpass);
            object obj = db.ExecuteScalar(ResetPassCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static int FindPass(string email, string newpass)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string FindPass = @"
            /* 1：成功 2：没有此会员 */
	        DECLARE @result AS INT
	        SET @result=1
        	
	        IF EXISTS(SELECT [MemberID] FROM [Member] WHERE [Email]=@Email)
	        BEGIN
		        UPDATE [Member] SET [Password]=@NewPass WHERE [Email]=@Email
		        SET @result=1
	        END
	        ELSE
	        BEGIN
		        SET @result=2
	        END
        	
	        SELECT @result";


            DbCommand ResetPassCommand = db.GetSqlStringCommand(FindPass);
            db.AddInParameter(ResetPassCommand, "@Email", DbType.String, email);
            db.AddInParameter(ResetPassCommand, "@NewPass", DbType.String, newpass);
            object obj = db.ExecuteScalar(ResetPassCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static MemberEntity GetMemberByValidateCode(string activationCode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            MemberEntity member = null;

            string SelectMember = @"
                    SELECT [Member].[MemberID],[Member].[Email],[Member].[Password],[Member].[ImgUrl],[Member].[Nickname],
                        [Member].[Name],[Member].[Sex],[Member].[Birthday],[Member].[QQ],[Member].[Phone],[Member].[ProvinceID],
                        [Member].[CityID],[Member].[AreaID],[Member].[Address],[Member].[Profession],[Member].[Education],
                        [Member].[Income],[Member].[Marriage],[Member].[LandingNumber],[Member].[LandDateTime],
                        [Member].[Created],[Member].[State],ProvinceArea.Name AS ProvinceName, CityArea.Name AS CityName, 
                        AreaArea.Name AS AreaName,[ValidateCode],[ValidateDateTime]
                    FROM [Member] LEFT OUTER JOIN
                      Area AS CityArea ON Member.CityID = CityArea.AreaID LEFT OUTER JOIN
                      Area AS AreaArea ON Member.AreaID = AreaArea.AreaID LEFT OUTER JOIN
                      Area AS ProvinceArea ON Member.ProvinceID = ProvinceArea.AreaID 
                    WHERE ValidateCode=@ValidateCode ";

            DbCommand SelectAdminCommand = db.GetSqlStringCommand(SelectMember);
            db.AddInParameter(SelectAdminCommand, "@ValidateCode", DbType.String, activationCode);

            using (IDataReader dr = db.ExecuteReader(SelectAdminCommand))
            {
                if (dr.Read())
                {
                    member = GetMemberByReader(dr);
                    member.ValidateCode = dr["ValidateCode"] == DBNull.Value ? null : dr["ValidateCode"].ToString();
                    member.ValidateDateTime = Util.ConverDateTime(dr["ValidateDateTime"]);
                }
            }

            return member;
        }

        internal static int UpdateMemberValidate(int memberID, string validateCode, DateTime validateDateTime)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateMember = @"UPDATE [Member] 
				 SET [ValidateCode]=@ValidateCode,[ValidateDateTime]=@ValidateDateTime
				 WHERE [MemberID] = @MemberID";

            DbCommand UpdateMemberCommand = db.GetSqlStringCommand(UpdateMember);
            db.AddInParameter(UpdateMemberCommand, "@MemberID", DbType.Int32, memberID);
            db.AddInParameter(UpdateMemberCommand, "@ValidateCode", DbType.String, validateCode);
            db.AddInParameter(UpdateMemberCommand, "@ValidateDateTime", DbType.DateTime, validateDateTime);

            result = db.ExecuteNonQuery(UpdateMemberCommand);

            return result;
        }
    }
}
